package com.winning.sx.microframework.dao;

import com.winning.dapi.data.api.IDataAccessService;
import com.winning.dapi.data.entity.DataTable;
import com.winning.sx.microframework.common.util.SqlTool;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;

import static com.winning.sx.microframework.common.util.Constains.ARTICAL_STATE_NEW;
import static com.winning.sx.microframework.common.util.Constains.ARTICAL_STATE_SUBMIT;

@Repository
public class EasyDao {
    @Autowired
    @Qualifier("dataSource")
    protected IDataAccessService iDataAccessService;
    final String SYS_VALIDFLAG_TRUE = "100001";
    final String SYS_VALIDFLAG_FALSE = "100002";

    public DataTable queryList(String TABLE_NAMNE, Map<String, String> param) throws SQLException {
        DataTable table = null;
        try {
            String sql = "SELECT * from " + TABLE_NAMNE + " where validFlag= '" + SYS_VALIDFLAG_TRUE + "' ";
            if (param != null) {
                for (String key : param.keySet()) {
                    String keyArray[] = key.split("_");
                    if (keyArray.length == 1) {
                        if (!"".equals(param.get(key)))
                            sql += " and " + key + "='" + param.get(key) + "'";
                    } else if (keyArray.length == 2) {

                        if (!"".equals(param.get(key)))
                            switch (keyArray[0].toCharArray()[0]) {
                                case 'q':
                                    sql += " and " + keyArray[1] + " = '" + param.get(key) + "'";
                                    break;
                                case 'b':
                                    sql += " and " + keyArray[1] + " >= '" + param.get(key) + "'";
                                    break;
                                case 'e':
                                    sql += " and " + keyArray[1] + " <= '" + param.get(key) + "'";
                                    break;
                                case 'L':
                                    sql += " and " + keyArray[1] + " like '%" + param.get(key) + "%'";
                                    break;
                                case 'l':
                                    sql += " and " + keyArray[1] + " like '" + param.get(key) + "%'";
                                    break;
                                default:
                                    sql += " and " + keyArray[1] + " = '" + param.get(key) + "'";

                            }
                    } else if (keyArray.length == 3) {
                        String logic = "";

                        if (!"".equals(param.get(key)))
                            switch (keyArray[1].toCharArray()[0]) {
                                case 'o':
                                    logic = " or ";
                                    break;
                                case 'a':
                                    logic = " and ";
                                    break;
                                default:
                                    logic = " and ";

                            }

                        if (!"".equals(param.get(key)))
                            switch (keyArray[0].toCharArray()[0]) {
                                case 'q':
                                    sql += (logic + keyArray[1] + " = '" + param.get(key) + "'");
                                    break;
                                case 'b':
                                    sql += (logic + keyArray[1] + " >= '" + param.get(key) + "'");
                                    break;
                                case 'e':
                                    sql += (logic + keyArray[1] + " <= '" + param.get(key) + "'");
                                    break;
                                case 'L':
                                    sql += (logic + keyArray[1] + " like '%" + param.get(key) + "%'");
                                    break;
                                case 'l':
                                    sql += (logic + keyArray[1] + " like '" + param.get(key) + "%'");
                                    break;
                                default:
                                    sql += (logic + keyArray[1] + " = '" + param.get(key) + "'");

                            }
                    }
                }
            }
//            sql += " order by createTime";
            table = iDataAccessService.querySql(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("获取信息失败！");
        }
        return table;
    }

    public Map queryEntity(String TABLE_NAMNE, Map<String, String> param) throws SQLException {
        DataTable table = null;
        try {
            String sql = "SELECT * from " + TABLE_NAMNE + " where validFlag= '" + SYS_VALIDFLAG_TRUE + "'";
            for (String key : param.keySet()) {
                if (!"".equals(param.get(key)))
                    if (!"".equals(param.get(key)))
                        sql += " and " + key + "='" + param.get(key) + "'";
            }
            table = iDataAccessService.querySql(sql);
            if (table.getTotalRowNum() < 1) {
                return null;
            } else if (table.getTotalRowNum() > 1) {
                throw new SQLException("数据库中存在多行被查询数据！");
            } else {
                return covertEntity(table.getDataRows().get(0));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("获取信息失败！");
        }
    }

    private Map<String, String> covertEntity(Map param) {
        Map<String, String> result = new HashMap<String, String>();
        param.forEach((key, value) -> {
            if (value instanceof String) {
                result.put(key.toString(), value.toString());
            } else if (value instanceof Integer) {
                result.put(key.toString(), value.toString());
            } else if (value instanceof Long) {
                result.put(key.toString(), value.toString());
            } else if (value instanceof Float) {
                result.put(key.toString(), value.toString());
            } else if (value instanceof Double) {
                result.put(key.toString(), value.toString());
            } else if (value instanceof Timestamp) {
                SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                result.put(key.toString(), format.format(value));
            }
        });
        return result;
    }

    public Map queryEntitys(String TABLE_NAMNE, Map<String, String> param) throws SQLException {
        DataTable table = null;
        try {
            String sql = "SELECT * from " + TABLE_NAMNE + " where ";
            for (String key : param.keySet()) {
                if (!"".equals(param.get(key)))
                    if (!"".equals(param.get(key)))
                        sql += " " + key + "='" + param.get(key) + "'";
            }
            table = iDataAccessService.querySql(sql);
            if (table.getTotalRowNum() < 1) {
                return null;
            } else if (table.getTotalRowNum() > 1) {
                throw new SQLException("数据库中存在多行被查询数据！");
            } else {
                return table.getDataRows().get(0);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("获取信息失败！");
        }
    }

    public boolean isExit(String TABLE_NAMNE, String idColumn, Map<String, String> param) throws SQLException {
        DataTable table = null;
        try {
            String sql = "SELECT * from " + TABLE_NAMNE + " where 1=1 ";
            for (String key : param.keySet()) {
                if (idColumn.equals(key)) {
                    sql += (" and " + key + "!='" + param.get(key) + "'");
                } else
                    sql += (" and " + key + "='" + param.get(key) + "'");
            }
            table = iDataAccessService.querySql(sql);
            if (table.getTotalRowNum() < 1) {
                return false;
            } else {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("获取信息失败！");
        }
    }

    public boolean insert(String TABLE_NAMNE, Map<String, String> param) throws SQLException {
        int result = -1;
        try {
            if (param == null || param.keySet().isEmpty()) {
                throw new SQLException("传入的数据为空，无法执行insert");
            }
            if (param.get("id") != null && !"".equals(param.get("id"))) {
                throw new SQLException("传入的数据带有主键，请使用update操作此数据！");
            }
            String sql = "set nocount on;" + "insert into " + TABLE_NAMNE + " ";
            String keys = "", values = "";
            for (String key : param.keySet()) {
                if (!"id".equals(key) && param.get(key) != null)
                    keys += ("," + key);
            }
            for (String key : param.keySet()) {
                if (!"id".equals(key) && param.get(key) != null) {
                    if ("null".equals(param.get(key).toString())) {
                        values += (",null");
                    } else
                        values += (",'" + param.get(key) + "'");
                }
            }
            sql += ("(" + keys.substring(1) + ",validFlag,createTime) values (" + values.substring(1) + ",'" + SYS_VALIDFLAG_TRUE + "'," + SqlTool.getSysTime() + ");" + "SELECT SCOPE_IDENTITY() as id;");
            DataTable dataTable = iDataAccessService.querySql(sql);
            String id = dataTable.getDataRows().get(0).get("id").toString();
            param.put("id", id);
            if (id == null) {
                return false;
            } else {
                return true;
            }
        } catch (SQLException e) {
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("插入信息失败！");
        }
    }

    public boolean backone(String TABLE_NAMNE, String idColumn, Map<String, String> param) throws SQLException {
        int result = -1;
        try {
            if (param == null || param.keySet().isEmpty()) {
                throw new SQLException("传入的数据为空，无法执行delete");
            }
            if (param.get("id") == null && "".equals(param.get("id"))) {
                throw new SQLException("传入的数据未带有主键！");
            }
            String sql = "update " + TABLE_NAMNE + " set state='" + ARTICAL_STATE_NEW + "'" + " where " + idColumn + "=" + String.valueOf(param.get(idColumn));
            result = iDataAccessService.executeSql(sql);
            if (result < 1) {
                return false;
            } else {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("更新信息失败！");
        }
    }

    public boolean updatestate(String TABLE_NAMNE, String idColumn, Map<String, String> param) throws SQLException {
        int result = -1;
        try {
            if (param == null || param.keySet().isEmpty()) {
                throw new SQLException("传入的数据为空，无法执行delete");
            }
            if (param.get("id") == null && "".equals(param.get("id"))) {
                throw new SQLException("传入的数据未带有主键！");
            }
            String sql = "update " + TABLE_NAMNE + " set state='" + ARTICAL_STATE_SUBMIT + "'" + " where " + idColumn + "=" + String.valueOf(param.get(idColumn));
            result = iDataAccessService.executeSql(sql);
            if (result < 1) {
                return false;
            } else {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("更新信息失败！");
        }
    }
    public boolean update(String TABLE_NAMNE, String idColumn, Map<String, String> param) throws SQLException {
        int result = -1;
        try {
            if (param == null || param.keySet().isEmpty()) {
                throw new SQLException("传入的数据为空，无法执行delete");
            }
            if (param.get("id") == null && "".equals(param.get("id"))) {
                throw new SQLException("传入的数据未带有主键！");
            }
            String sql = "update " + TABLE_NAMNE + " set ";
            String updateSet = "";
            param.remove("updateTime");
            for (String key : param.keySet()) {
                if (!idColumn.equals(key) && param.get(key) != null) {
                    if ("null".equals(param.get(key).toString())) {
                        updateSet += ("," + key + "= null ");
                    } else
                        updateSet += ("," + key + "='" + param.get(key).toString() + "'");
                }

            }
            if (updateSet.length() == 0) return true;
            sql += (updateSet.substring(1) + ",updateTime=" + SqlTool.getSysTime() + " where " + idColumn + "=" + param.get(idColumn));
            result = iDataAccessService.executeSql(sql);
            if (result < 1) {
                return false;
            } else {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("更新信息失败！");
        }
    }

    public boolean update(String TABLE_NAMNE, Map<String, String> queryParam, Map<String, String> param) throws SQLException {
        int result = -1;
        try {
            if (param == null || param.keySet().isEmpty()) {
                throw new SQLException("传入的数据为空，无法执行delete");
            }
            if (param.get("id") == null && "".equals(param.get("id"))) {
                throw new SQLException("传入的数据未带有主键！");
            }
            String sql = "update " + TABLE_NAMNE + " set ";
            String updateSet = "";
            for (String key : param.keySet()) {
                String value = param.get(key);
                updateSet += ("," + key + "='" + value + "'");
            }
            if (updateSet.length() == 0) return true;
            String updateWhere = "";
            for (String key : queryParam.keySet()) {
                String value = queryParam.get(key);
                updateWhere += (" and " + key + "='" + value + "'");
            }
            if (updateWhere.length() == 0) return true;
            sql += (updateSet.substring(1) + ",updateTime=" + SqlTool.getSysTime() + " where 1=1 " + updateWhere);
            result = iDataAccessService.executeSql(sql);
            if (result < 1) {
                return false;
            } else {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("更新信息失败！");
        }
    }

    public boolean delete(String TABLE_NAMNE, String idColumn, Map<String, String> param) throws SQLException {
        int result = -1;
        try {
            if (param == null || param.keySet().isEmpty()) {
                throw new SQLException("传入的数据为空，无法执行delete");
            }
            if (param.get(idColumn) == null && "".equals(param.get(idColumn))) {
                throw new SQLException("传入的数据未带有主键！");
            }
            String ids=String.valueOf(param.get(idColumn));
            ids="'"+ids+"'";
            ids=ids.replaceAll(",","','");
            String sql = "update " + TABLE_NAMNE + " set validFlag='" + SYS_VALIDFLAG_FALSE + "' ,updateTime=" + SqlTool.getSysTime() + " where " + idColumn + " in (" + ids + ")";
            result = iDataAccessService.executeSql(sql);
            if (result < 1) {
                return false;
            } else {
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("删除信息失败！");

        }
    }

    public boolean deleteAll(String TABLE_NAMNE) throws SQLException {
        int result = -1;
        try {
            String sql = "update " + TABLE_NAMNE + " set validFlag='" + SYS_VALIDFLAG_FALSE + "' ,updateTime=" + SqlTool.getSysTime();
            result = iDataAccessService.executeSql(sql);
            if (result == -1) {
                return false;
            } else {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("删除信息失败！");
        }
    }

    public boolean deleteDeep(String sql, Map param) throws SQLException {
        int result = -1;
        try {
            result = iDataAccessService.executeSqlUsingParam(sql, param);
            if (result == -1) {
                return false;
            } else {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("删除信息失败！");
        }
    }

    public DataTable querySql(String veiwName, String columns, Map<String, String> param, String where, String orderBy, String groupBy) throws SQLException {
        DataTable table = null;
        try {
            String sql = "SELECT " + columns + " from " + veiwName;
            if (param != null) {
                sql += " where 1=1 ";
                for (String key : param.keySet()) {
                    String keyArray[] = key.split("_");
                    if (keyArray.length == 1) {
                        if (!"".equals(param.get(key)))
                            sql += " and " + key + "='" + param.get(key) + "'";
                    } else if (keyArray.length == 2) {

                        if (!"".equals(param.get(key)))
                            switch (keyArray[0].toCharArray()[0]) {
                                case 'q':
                                    sql += " and " + keyArray[1] + " = '" + param.get(key) + "'";
                                    break;
                                case 'b':
                                    sql += " and " + keyArray[1] + " >= '" + param.get(key) + "'";
                                    break;
                                case 'e':
                                    sql += " and " + keyArray[1] + " <= '" + param.get(key) + "'";
                                    break;
                                case 'L':
                                    sql += " and " + keyArray[1] + " like '%" + param.get(key) + "%'";
                                    break;
                                case 'l':
                                    sql += " and " + keyArray[1] + " like '" + param.get(key) + "%'";
                                    break;
                                default:
                                    sql += " and " + keyArray[1] + " = '" + param.get(key) + "'";

                            }
                    } else if (keyArray.length == 3) {
                        String logic = "";

                        if (!"".equals(param.get(key)))
                            switch (keyArray[1].toCharArray()[0]) {
                                case 'o':
                                    logic = " or ";
                                    break;
                                case 'a':
                                    logic = " and ";
                                    break;
                                default:
                                    logic = " and ";

                            }

                        if (!"".equals(param.get(key)))
                            switch (keyArray[0].toCharArray()[0]) {
                                case 'q':
                                    sql += (logic + keyArray[1] + " = '" + param.get(key) + "'");
                                    break;
                                case 'b':
                                    sql += (logic + keyArray[1] + " >= '" + param.get(key) + "'");
                                    break;
                                case 'e':
                                    sql += (logic + keyArray[1] + " <= '" + param.get(key) + "'");
                                    break;
                                case 'L':
                                    sql += (logic + keyArray[1] + " like '%" + param.get(key) + "%'");
                                    break;
                                case 'l':
                                    sql += (logic + keyArray[1] + " like '" + param.get(key) + "%'");
                                    break;
                                default:
                                    sql += (logic + keyArray[1] + " = '" + param.get(key) + "'");

                            }
                    }

                }
            }
            if (where != null && !"".equals(where))
                sql += where;
            if (groupBy != null && !"".equals(groupBy))
                sql += " group by " + groupBy;
            if (orderBy != null && !"".equals(orderBy))
                sql += " order by " + orderBy;
            System.out.println("执行sql:" + sql);
            table = iDataAccessService.querySql(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("获取信息失败！");
        }
        return table;
    }

    public DataTable querySql(String sqlStr, Map<String, String> param) throws SQLException {
        DataTable table = null;
        try {
            System.out.println("执行sql:" + sqlStr);
            printMap(param);
            table = iDataAccessService.querySqlUsingParam(sqlStr, param);
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("获取信息失败！");
        }
        return table;
    }

    public DataTable querySqlByParam(String sqlStr, Map<String, Object> param) throws SQLException {
        DataTable table = null;
        try {
            table = iDataAccessService.querySqlUsingParam(sqlStr, param);
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("获取信息失败！");
        }
        return table;
    }

    public DataTable querySqlInPage(String sqlStr, Map<String, String> param, int pageSize, int pageStart) throws SQLException {
        DataTable table = null;
        try {
            table = iDataAccessService.querySqlUsingParamInPage(sqlStr, pageStart, pageSize, param);
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("获取信息失败！");
        }
        return table;
    }

    public DataTable querySqlInPage(String veiwName, String columns, Map<String, String> param, String where, String orderBy, String groupBy, int pageSize, int pageStart) throws SQLException {
        DataTable table = null;
        try {
            String sql = "SELECT " + columns + " from " + veiwName;
            if (param != null) {
                sql += " where 1=1 ";
                for (String key : param.keySet()) {
                    String keyArray[] = key.split("_");
                    if (keyArray.length == 1) {
                        if (!"".equals(param.get(key)))
                            sql += " and " + key + "='" + param.get(key) + "'";
                    } else if (keyArray.length == 2) {

                        if (!"".equals(param.get(key)))
                            switch (keyArray[0].toCharArray()[0]) {
                                case 'q':
                                    sql += " and " + keyArray[1] + " = '" + param.get(key) + "'";
                                    break;
                                case 'b':
                                    sql += " and " + keyArray[1] + " >= '" + param.get(key) + "'";
                                    break;
                                case 'e':
                                    sql += " and " + keyArray[1] + " <= '" + param.get(key) + "'";
                                    break;
                                case 'L':
                                    sql += " and " + keyArray[1] + " like '%" + param.get(key) + "%'";
                                    break;
                                case 'l':
                                    sql += " and " + keyArray[1] + " like '" + param.get(key) + "%'";
                                    break;
                                default:
                                    sql += " and " + keyArray[1] + " = '" + param.get(key) + "'";

                            }
                    } else if (keyArray.length == 3) {
                        String logic = "";

                        if (!"".equals(param.get(key)))
                            switch (keyArray[1].toCharArray()[0]) {
                                case 'o':
                                    logic = " or ";
                                    break;
                                case 'a':
                                    logic = " and ";
                                    break;
                                default:
                                    logic = " and ";

                            }

                        if (!"".equals(param.get(key)))
                            switch (keyArray[0].toCharArray()[0]) {
                                case 'q':
                                    sql += (logic + keyArray[1] + " = '" + param.get(key) + "'");
                                    break;
                                case 'b':
                                    sql += (logic + keyArray[1] + " >= '" + param.get(key) + "'");
                                    break;
                                case 'e':
                                    sql += (logic + keyArray[1] + " <= '" + param.get(key) + "'");
                                    break;
                                case 'L':
                                    sql += (logic + keyArray[1] + " like '%" + param.get(key) + "%'");
                                    break;
                                case 'l':
                                    sql += (logic + keyArray[1] + " like '" + param.get(key) + "%'");
                                    break;
                                default:
                                    sql += (logic + keyArray[1] + " = '" + param.get(key) + "'");

                            }
                    }

                }
            }
            if (where != null && !"".equals(where))
                sql += where;
            if (groupBy != null && !"".equals(groupBy))
                sql += " group by " + groupBy;
            if (orderBy != null && !"".equals(orderBy))
                sql += " order by " + orderBy;
            pageStart = (pageStart - 1) * pageSize;
            System.out.println("执行sql:" + sql);
            table = iDataAccessService.querySqlInPage(sql, pageStart, pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } catch (Exception e) {
            e.printStackTrace();
            throw new SQLException("获取信息失败！");
        }
        return table;
    }

    private void printMap(Map<String, String> param) {
        for (String key : param.keySet()) {
            System.out.println(key + ":" + param.get(key));
        }
    }
}
